1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the optimization of queries by dynamic selection of optimal grouping sequence at runtime for grouping sets, rollup and cube operations in SQL query processing.
2. Description of Related Art
Computer systems incorporating Relational DataBase Management System (RDBMS) software using the Structured Query Language (SQL) interface are well known in the art. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
To better support business intelligence (BI) and online analytic processing (OLAP) applications, a set of grouping operations, in addition to the standard GROUP BY clause, have recently been included into SQL, to allow users to create various groupings of data in a single SQL query. The set of grouping operations include GROUPING SETS, ROLLUP, CUBE and a scalar GROUPING function.
The GROUP BY clause specifies how the set of rows is to be sub-divided or grouped. Rows with the same values for the expressions in the group by clause are grouped together. GROUPING SETS allows the user to specify several groupings of data are to be calculated, including a “subtotal” row. ROLLUP and CUBE are special cases of the generic GROUPING SETS operation, which can be thought of as “super group” operations because they provide many groupings in a single statement. In addition, a new scalar function, GROUPING, is provided to work in conjunction with these extensions.
What is a needed in the art are techniques that optimize the execution of queries containing a GROUP BY clause with one or more GROUPING SETS, ROLLUP and CUBE operations with or without scalar GROUPING functions. Specifically, there is a need in the art for techniques that avoid unnecessary steps in the execution of such queries. The present invention satisfies these needs.